/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apt.Bean.LectureEventEntities;
import org.apt.connectDB.MyConnect;

/**
 *
 * @author Administrator
 */
public class LectureEventDao {

    /**
     * function  Get 10 record per onne caccess
     * @param pageno : number of page )
     * @return list LectureEvent
     */
    public static List<LectureEventEntities> getLectureEvent(int pageno) {
        List<LectureEventEntities> lectureventlist = new ArrayList<LectureEventEntities>();
        int pageno2;
        try {
            if (pageno <= 1) {
                pageno = 1;
            } else if (pageno == 2) {
                pageno = 10 + 1;
            } else if (pageno > 2) {
                pageno = (pageno - 1) * 10 + 1;
            }
            pageno2 = pageno + 9;
            Connection con = MyConnect.getConnection();
            Statement st = con.createStatement();
            String sql = "SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY EventID) AS row,* FROM LectureEvent)  as tblTemp WHERE  row between " + pageno + " AND " + pageno2 + " ";
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                LectureEventEntities lecture = new LectureEventEntities();
                lecture.setEventID(rs.getInt("EventID"));
                lecture.setEventName(rs.getString("EventName"));
                lecture.setImage(rs.getString("Image"));
                lecture.setStart(rs.getString("Start"));
                lecture.setEnd(rs.getString("End"));
                lecture.setEventDay(rs.getString("EventDay"));
                lecture.setLocation(rs.getString("Location"));
                lecture.setContent(rs.getString("Content"));
                lecture.setPrize(rs.getString("Prize"));
                lecture.setPrizeJoin(rs.getFloat("PriceJoin"));
                lecture.setCurrentJoin(rs.getInt("CurrentJoin"));
                lecture.setMaxJoin(rs.getInt("MaxJoin"));
                lecture.setStatus(rs.getBoolean("Status"));
                lecture.setDescription(rs.getString("Description"));
                lectureventlist.add(lecture);
            }
        } catch (SQLException ex) {
            Logger.getLogger(LectureEventDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        }
        return lectureventlist;
    }

    /**
     * function  Get 10 record per onne caccess
     * @param pageno : number of page )
     * @return list LectureEvent
     */
    public static List<LectureEventEntities> SearchLectureEventByName(String eventName) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<LectureEventEntities> lectureventlist = new ArrayList<LectureEventEntities>();
        try {
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT top 10 * FROM  LectureEvent WHERE EventName LIKE N'" + eventName + "%'";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                LectureEventEntities lecture = new LectureEventEntities();
                lecture.setEventID(rs.getInt("EventID"));
                lecture.setEventName(rs.getString("EventName"));
                lecture.setImage(rs.getString("Image"));
                lecture.setStart(rs.getString("Start"));
                lecture.setEnd(rs.getString("End"));
                lecture.setEventDay(rs.getString("EventDay"));
                lecture.setLocation(rs.getString("Location"));
                lecture.setContent(rs.getString("Content"));
                lecture.setPrize(rs.getString("Prize"));
                lecture.setPrizeJoin(rs.getFloat("PriceJoin"));
                lecture.setCurrentJoin(rs.getInt("CurrentJoin"));
                lecture.setMaxJoin(rs.getInt("MaxJoin"));
                lecture.setStatus(rs.getBoolean("Status"));
                lecture.setDescription(rs.getString("Description"));
                lectureventlist.add(lecture);
            }
        } catch (SQLException ex) {
            Logger.getLogger(LectureEventDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return lectureventlist;
    }

    /**
     * function get top 5 Lecture Event
     * @return list 5 Lecture top
     */
    public static List<LectureEventEntities> GetTop5LectureEvent() {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<LectureEventEntities> lectureventlist = new ArrayList<LectureEventEntities>();
        try {
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT top 5 * FROM  LectureEvent order by EventID ";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                LectureEventEntities lecture = new LectureEventEntities();
                lecture.setEventID(rs.getInt("EventID"));
                lecture.setEventName(rs.getString("EventName"));
                lecture.setImage(rs.getString("Image"));
                lecture.setStart(rs.getString("Start"));
                lecture.setEnd(rs.getString("End"));
                lecture.setEventDay(rs.getString("EventDay"));
                lecture.setLocation(rs.getString("Location"));
                lecture.setContent(rs.getString("Content"));
                lecture.setPrize(rs.getString("Prize"));
                lecture.setPrizeJoin(rs.getFloat("PriceJoin"));
                lecture.setCurrentJoin(rs.getInt("CurrentJoin"));
                lecture.setMaxJoin(rs.getInt("MaxJoin"));
                lecture.setStatus(rs.getBoolean("Status"));
                lecture.setDescription(rs.getString("Description"));
                lectureventlist.add(lecture);
            }
        } catch (SQLException ex) {
            Logger.getLogger(LectureEventDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return lectureventlist;
    }

    /**
     * functiion get One LectureEvent
     * @param seminarID
     * @return list of LectureEvent
     */
    public static LectureEventEntities getLecturEventByID(int seminarID) {
        LectureEventEntities lecturevent = new LectureEventEntities();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String sql = "select * FROM LectureEvent where EventID=" + seminarID + "";
            con = MyConnect.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                lecturevent.setEventID(rs.getInt("EventID"));
                lecturevent.setEventName(rs.getString("EventName"));
                lecturevent.setImage(rs.getString("Image"));
                lecturevent.setStart(rs.getString("Start"));
                lecturevent.setEnd(rs.getString("End"));
                lecturevent.setEventDay(rs.getString("EventDay"));
                lecturevent.setLocation(rs.getString("Location"));
                lecturevent.setContent(rs.getString("Content"));
                lecturevent.setPrize(rs.getString("Prize"));
                lecturevent.setPrizeJoin(rs.getFloat("PriceJoin"));
                lecturevent.setCurrentJoin(rs.getInt("CurrentJoin"));
                lecturevent.setMaxJoin(rs.getInt("MaxJoin"));
                lecturevent.setStatus(rs.getBoolean("Status"));
                lecturevent.setDescription(rs.getString("Description"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDao.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return lecturevent;
    }

    /***
     * function get lectureEvent By username and status
     * @param username
     * @param status
     * @return List LectureEvent
     */
    public static List<LectureEventEntities> getLectureEventMemberRegister(String username, String status) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<LectureEventEntities> lectureventlist = new ArrayList<LectureEventEntities>();
        try {
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT     LectureEvent.*, PaymentLecture.ID, PaymentLecture.Status AS Expr1 "
                    + "FROM LectureEvent INNER JOIN PaymentLecture ON LectureEvent.EventID = PaymentLecture.EventID "
                    + "where PaymentLecture.[User]= '" + username + "' and PaymentLecture.StatusPayment='" + status + "'";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                LectureEventEntities lecture = new LectureEventEntities();
                lecture.setEventID(rs.getInt("EventID"));
                lecture.setEventName(rs.getString("EventName"));
                lecture.setImage(rs.getString("Image"));
                lecture.setStart(rs.getString("Start"));
                lecture.setEnd(rs.getString("End"));
                lecture.setEventDay(rs.getString("EventDay"));
                lecture.setLocation(rs.getString("Location"));
                lecture.setContent(rs.getString("Content"));
                lecture.setPrize(rs.getString("Prize"));
                lecture.setPrizeJoin(rs.getFloat("PriceJoin"));
                lecture.setCurrentJoin(rs.getInt("CurrentJoin"));
                lecture.setMaxJoin(rs.getInt("MaxJoin"));
                lecture.setStatus(rs.getBoolean("Status"));
                lecture.setDescription(rs.getString("Description"));
                lectureventlist.add(lecture);
            }
        } catch (SQLException ex) {
            Logger.getLogger(LectureEventDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return lectureventlist;
    }

    /**
     * function check Register
     * @param lectureEventID
     * @return true if ok or false
     */
    public static boolean CheckCurrentJoinRegisterLectureEvent(int lectureEventID) {
        boolean resuilt = false;
        try {
            LectureEventEntities lecturevent = getLecturEventByID(lectureEventID);
            String datecompare = lecturevent.getEventDay();
            String datenow = new java.sql.Date(System.currentTimeMillis()).toString();
            String[] dnow = datenow.split("-");
            String[] dEvent = datecompare.split("-");
            if (Integer.parseInt(dnow[0]) <= Integer.parseInt(dEvent[2])) {
                if (Integer.parseInt(dnow[0]) == Integer.parseInt(dEvent[2])) {
                    resuilt = true;
                    if (Integer.parseInt(dnow[1]) <= Integer.parseInt(dEvent[1])) {
                        resuilt = true;
                        if (Integer.parseInt(dnow[2]) <= Integer.parseInt(dEvent[0])) {
                            resuilt = true;
                            if (lecturevent.getCurrentJoin() < lecturevent.getMaxJoin()) {
                                resuilt = true;
                            } else {
                                resuilt = false;
                            }
                        } else {
                            resuilt = false;
                        }
                    } else {
                        resuilt = false;
                    }
                } else {
                    if (lecturevent.getCurrentJoin() < lecturevent.getMaxJoin()) {
                        resuilt = true;
                    } else {
                        resuilt = false;
                    }
                }
            } else {
                resuilt = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resuilt;
    }

    public static void main(String[] args) {
        String datenow = new java.sql.Date(System.currentTimeMillis()).toString();
        boolean a = CheckCurrentJoinRegisterLectureEvent(1);
        System.out.println(a);
    }
}
